package com.example.mapper.aboutStudent;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.vo.request.MajorGradeSumTemp;
import com.example.vo.request.ProvinceSumTemp;
import com.example.pojo.aboutStudent.Student;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface StudentMapper extends BaseMapper<Student> {

    @Select("SELECT origin_area, AVG(total_points) as avg_points, MAX(total_points) as max_points, MIN(total_points) as min_points, count(id) as sum\n" +
            "FROM student \n" +
            "WHERE college = #{college} \n" +
            "AND grade = #{grade} GROUP BY origin_area;")
    List<ProvinceSumTemp> getCollegePoints(String college, String grade);

    @Select("SELECT origin_area, AVG(total_points) as avg_points, MAX(total_points) as max_points, MIN(total_points) as min_points, count(id) as sum\n" +
            "FROM student \n" +
            "WHERE college = #{college} \n" +
            "and major = #{major} \n" +
            "AND grade = #{grade} GROUP BY origin_area;")
    List<ProvinceSumTemp> getMajorPoints(String college, String major, String grade);


    @Select("SELECT grade, AVG(total_points) as avg_points, MAX(total_points) as max_points, MIN(total_points) as min_points, count(id) as sum\n" +
            "FROM student \n" +
            "WHERE major = #{major} \n" +
            "GROUP BY grade;")
    List<MajorGradeSumTemp> getMajorGradePoints(String major);
}
